﻿using Groep2TINGHService.Get_Set_Classes;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Groep2TINGHService.DB_Classes
{
    public class CompanyDB
    {
        //Geeft een lijst terug met alle Companies
        public static List<Company> GetAllCompanies()
        {
            List<Company> lijst = new List<Company>();
            SqlConnection connection = ConnectionDB.GetConnection();
            SqlCommand selectcommand = new SqlCommand("select * from Company", connection);
            try
            {
                Company temp;
                connection.Open();
                SqlDataReader reader = selectcommand.ExecuteReader();
                int Id = reader.GetOrdinal("Id");
                int Name = reader.GetOrdinal("Name");
                int Street = reader.GetOrdinal("Street");
                int ZipCode = reader.GetOrdinal("ZipCode");
                int City = reader.GetOrdinal("City");
                int Country = reader.GetOrdinal("Country");
                int Email = reader.GetOrdinal("Email");
                int Phone = reader.GetOrdinal("Phone");
                int Employees = reader.GetOrdinal("Employees");

                while (reader.Read())
                {
                    temp = new Company();
                    temp.Id = reader.GetGuid(Id);
                    temp.Name = reader.GetString(Name);
                    temp.Street = reader.GetString(Street);
                    temp.ZipCode = reader.GetInt32(ZipCode);
                    temp.City = reader.GetString(City);
                    temp.Country = reader.GetString(Country);
                    temp.Email = reader.GetString(Email);
                    temp.Phone = reader.GetString(Phone);
                    temp.Employees = reader.GetInt32(Employees);
                    lijst.Add(temp);

                }
                reader.Close();
                return lijst;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        //Voegt een nieuwe Company toe
        public static int InsertCompany(Company company)
        {
            SqlConnection connection = ConnectionDB.GetConnection();
            SqlCommand selectCommand = new SqlCommand("insert into Company(Name,Street,Zipcode,City,Country,Email,Phone,Employees)" +
                            "Values(@Name,@Street,@Zipcode,@City,@Country,@Email,@Phone,@Employees);",connection);
            selectCommand.Parameters.AddWithValue("@Name", company.Name);
            selectCommand.Parameters.AddWithValue("@Street", company.Street);
            selectCommand.Parameters.AddWithValue("@Zipcode", company.ZipCode);
            selectCommand.Parameters.AddWithValue("@City", company.City);
            selectCommand.Parameters.AddWithValue("@Country", company.Country);
            selectCommand.Parameters.AddWithValue("@Email", company.Email);
            selectCommand.Parameters.AddWithValue("@Phone", company.Phone);
            selectCommand.Parameters.AddWithValue("@Employees", company.Employees);

            try
            {
                    connection.Open();
                    int x = selectCommand.ExecuteNonQuery();
                    return x;
                }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

        }


        //Verwijdert een Company via Id
        public static int DeleteCompany(Company company)
        {
            //verwijderen in tabel Company
            SqlConnection connection = ConnectionDB.GetConnection();
            string delete = "DELETE FROM Company WHERE Name = @name";
            SqlCommand deleteCommand = new SqlCommand(delete, connection);
            deleteCommand.Parameters.AddWithValue("@name", company.Name);

            //verwijderen in Membership
            string deleteMembership = "DELETE m FROM aspnet_Membership m, aspnet_Users u WHERE m.UserId = u.UserId and u.UserName = @name";
            SqlCommand deleteMembershipCommand = new SqlCommand(deleteMembership, connection);
            deleteMembershipCommand.Parameters.AddWithValue("@name", company.Name);

            //Verwijderen in UsersInroles
            string deleteUsersInRoles = "DELETE ur FROM aspnet_UsersInRoles ur, aspnet_Users u WHERE ur.UserId = u.UserId and u.UserName = @name";
            SqlCommand deleteUsersInRolesCommand = new SqlCommand(deleteUsersInRoles, connection);
            deleteUsersInRolesCommand.Parameters.AddWithValue("@name", company.Name);

            //Verwijderen in Users
            string deleteUsers = "DELETE u FROM aspnet_Users u WHERE u.UserName = @name";
            SqlCommand deleteUsersCommand = new SqlCommand(deleteUsers, connection);
            deleteUsersCommand.Parameters.AddWithValue("@name", company.Name);

            try
            {
                connection.Open();
                int x = deleteCommand.ExecuteNonQuery();
                deleteMembershipCommand.ExecuteNonQuery();
                deleteUsersInRolesCommand.ExecuteNonQuery();
                deleteUsersCommand.ExecuteNonQuery();
                return x;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        //Wijzigen van een Company
        public static int ModifyCompany(Company company)
        {
            //Update voor Company tabel
            SqlConnection connection = ConnectionDB.GetConnection();
            string modify = "UPDATE Company "+ 
                            "SET Street=@Street, Zipcode=@Zipcode, City=@City, Country=@Country, Email=@Email, Phone=@Phone, Employees=@Employees "+ 
                            "WHERE Name = @Name";
            SqlCommand modifyCommand = new SqlCommand(modify, connection);
            modifyCommand.Parameters.AddWithValue("@Name", company.Name);
            modifyCommand.Parameters.AddWithValue("@Street", company.Street);
            modifyCommand.Parameters.AddWithValue("@Zipcode", company.ZipCode);
            modifyCommand.Parameters.AddWithValue("@City", company.City);
            modifyCommand.Parameters.AddWithValue("@Country", company.Country);
            modifyCommand.Parameters.AddWithValue("@Email", company.Email);
            modifyCommand.Parameters.AddWithValue("@Phone", company.Phone);
            modifyCommand.Parameters.AddWithValue("@Employees", company.Employees);

            //update voor Membership tabel
            string modifyMembership = "UPDATE aspnet_Membership " +
                                      "SET Email=@Email " +
                                      "FROM aspnet_Users u, aspnet_Membership m " +
                                      "WHERE u.UserId = m.UserId and u.UserName = @Name";
            SqlCommand modifyMembershipCommand = new SqlCommand(modifyMembership, connection);
            modifyMembershipCommand.Parameters.AddWithValue("@Name", company.Name);
            modifyMembershipCommand.Parameters.AddWithValue("@Email", company.Email);


            try
            {
                connection.Open();
                int x = modifyCommand.ExecuteNonQuery();
                modifyMembershipCommand.ExecuteNonQuery();
                return x;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
        public static Company GetCompanyByName(string name)
        {
            Company temp = new Company();
            String sql = "SELECT * FROM Company WHERE Name = @name";
            SqlConnection connection = ConnectionDB.GetConnection();
            SqlCommand selectCommand = new SqlCommand(sql, connection);
            selectCommand.Parameters.AddWithValue("@name", name);
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                if (reader.Read())
                {
                    int Street = reader.GetOrdinal("Street");
                    int Zipcode = reader.GetOrdinal("Zipcode");
                    int City = reader.GetOrdinal("City");
                    int Country = reader.GetOrdinal("Country");
                    int Email = reader.GetOrdinal("Email");
                    int Phone = reader.GetOrdinal("Phone");
                    int Employees = reader.GetOrdinal("Employees");

                    temp.Street = reader.GetString(Street);
                    temp.ZipCode = reader.GetInt32(Zipcode);
                    temp.City = reader.GetString(City);
                    temp.Country = reader.GetString(Country);
                    temp.Email = reader.GetString(Email);
                    temp.Phone = reader.GetString(Phone);
                    temp.Employees = reader.GetInt32(Employees);
                }
                reader.Close();
                return temp;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}